﻿CREATE FUNCTION [sp_ssiscatalog].[get_event_message_context_as_XML]
(
	@event_message_id	BIGINT
)
RETURNS	TABLE AS
/*
Following index will make a world of difference to this function:
USE [SSISDB]
GO
CREATE NONCLUSTERED INDEX [jt_test_index]
ON [internal].[event_message_context] ([event_message_id])
INCLUDE ([operation_id],[context_depth],[package_path],[context_type],[context_source_name],[property_name],[property_value])
*/
		RETURN
		WITH	cte ([event_message_context_xml]) AS (
				SELECT	[event_message_context].[property_name],[event_message_context].[property_value],[event_message_context].[context_depth],[event_message_context].[package_path],[event_message_context].[context_type_desc],[event_message_context].[context_source_name]
				FROM	(
						SELECT	emc.[context_depth],emc.[package_path],ctd.[context_type_desc],emc.[context_source_name],emc.[property_name],emc.[property_value]
						FROM	[$(SSISDB)].[catalog].[event_message_context]emc
						CROSS APPLY [sp_ssiscatalog].[get_context_type_desc_from_context_type](emc.[context_type])ctd
						WHERE	emc.[event_message_id] = @event_message_id
						)[event_message_context]
				FOR XML AUTO
				)
		SELECT	[event_message_context_xml]	=	CONVERT(XML,[event_message_context_xml])
		FROM	[cte]
